Imports System.Data
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

Partial Class Mngt_Info_Generate
    Inherits System.Web.UI.Page

    Const RptPath As String = "~/Management_Info/"

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim pFunc As New PolyFunc.Secure
            Dim dao As New DAOReport.REPORT_MSTR_DAO
            Dim vo As New DAOReport.REPORT_MSTR_VO

            vo.REPORT_CD = Trim(pFunc.DecodeBase64(Request.QueryString("rid")))
            Dim dt As DataTable = dao.Search(vo)

            If dt.Rows.Count > 0 Then
                ViewState("report_table") = dt.Rows(0)("REPORT_TABLE")
                ViewState("report_path") = RptPath & dt.Rows(0)("REPORT_PATH")
                lblTitle.Text = dt.Rows(0)("REPORT_NAME")
                lblTitleDate.Text = dt.Rows(0)("REPORT_NAME")

                Select Case ViewState("report_table")
                    Case "aging_unpaid_premium"
                        Panel_Search.Visible = True

                    Case "vw_rpt_masterlist"
                        Panel_Date_Search.Visible = True

                        tblDate.Visible = False

                        Dim lk As New LookUp.FillLookUp
                        lk.FillDropDown(Me.Status_cmb, LookUp.FillLookUp.eLookUpType.other, "POLICY_STATUS")
                        lk = Nothing

                    Case "birthday_list"
                        Panel_Date_Search.Visible = True

                        Dim lk As New LookUp.FillLookUp
                        lk.FillDropDown(Me.Status_cmb, LookUp.FillLookUp.eLookUpType.other, "POLICY_STATUS")
                        lk = Nothing


                    Case "vw_policy_issue_dtl_search"
                        Panel_Date_Search.Visible = True
                        Dim lk As New LookUp.FillLookUp
                        lk.FillDropDown(Me.Status_cmb, LookUp.FillLookUp.eLookUpType.other, "ISSUE_STATUS")
                        lk = Nothing

                    Case "vw_rpt_commision_journal"

                        Panel_Date_Search.Visible = True

                        tblPolicy.Visible = False
                        tblProduct.Visible = False

                        Dim lk As New LookUp.FillLookUp
                        lk.FillDropDown(Me.Status_cmb, LookUp.FillLookUp.eLookUpType.other, "COMMIS_STAT")
                        lk = Nothing

                    Case "vw_rpt_mailmerge", "vw_rpt_monthly_renewal"
                        Panel_Date_Search.Visible = True

                        tdStatus.InnerText = ""
                        tblPolicy.Visible = False
                        Status_cmb.Visible = False

                    Case "vw_rpt_policy_submit_dtl"

                        Panel_Date_Search.Visible = True

                        Dim lk As New LookUp.FillLookUp
                        lk.FillDropDown(Me.Status_cmb, LookUp.FillLookUp.eLookUpType.other, "POLICY_STATUS")
                        lk = Nothing

                End Select

            End If

            dao = Nothing
            vo = Nothing
            pFunc = Nothing
        End If
        
    End Sub

    Protected Sub btnGenerate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerate.Click
        Select Case ViewState("report_table")
            Case "aging_unpaid_premium"
                GenerateAgingList()
        End Select
    End Sub

    Protected Sub btnIssue_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnIssue.Click
        Select Case ViewState("report_table")
            Case "vw_rpt_masterlist"
                GenerateMasterList()
            Case "vw_policy_issue_dtl_search"
                GenerateIssues()
            Case "vw_rpt_commision_journal"
                GenerateCommis()
            Case "vw_rpt_mailmerge"
                GenerateMonthly()
            Case "vw_rpt_policy_submit_dtl"
                GeneratePolicySubmit()
            Case "birthday_list"
                GenerateBirthdayList()
            Case "vw_rpt_monthly_renewal"
                GenerateRenewal()
        End Select
    End Sub

    Private Sub RenderReport(ByVal sDS As DataSet, ByVal TableName As String, ByVal ReportName As String)
        Dim sConn As New Data.SqlClient.SqlConnection
        Dim sCom As New Data.SqlClient.SqlCommand
        Dim sDApt As New Data.SqlClient.SqlDataAdapter(sCom)

        Try

            sDS.Tables(0).TableName = TableName

            Dim objReport As New ReportDocument

            'MsgBox(Server.MapPath(Session("sqlUseReport").ToString))
            objReport.Load(Server.MapPath(ReportName))

            'objReport.PrintOptions.PaperOrientation = PaperOrientation.Landscape
            objReport.SetDataSource(sDS)

            Dim expReport As ExportOptions
            expReport = objReport.ExportOptions
            expReport.FormatOptions = New PdfRtfWordFormatOptions 'PDF format option
            expReport.ExportFormatType = ExportFormatType.PortableDocFormat 'Set the format type of document
            Dim req As ExportRequestContext = New ExportRequestContext
            req.ExportInfo = expReport

            'Sets the report to HTML stream
            Dim st As System.IO.Stream
            st = objReport.FormatEngine.ExportToStream(req)

            objReport = Nothing 'Dispose the report

            'Stream content
            Response.ClearContent()
            Response.ClearHeaders()
            Response.ContentType = "application/pdf"

            Dim b(st.Length) As Byte
            st.Read(b, 0, st.Length) 'Read Stream
            Response.BinaryWrite(b) 'Write Stream
            st.Close() 'Close Stream
            st = Nothing 'Dispose

            Response.End()

        Catch ex As Exception

        Finally
            sConn.Close()
        End Try
    End Sub

    Private Sub DoRender(ByVal dt As DataTable)
        Dim sds As New DataSet

        sds.Tables.Add(dt)

        Call RenderReport(sds, ViewState("report_table"), ViewState("report_path"))

        sds = Nothing
    End Sub

#Region "Aging of Unpaid Premium"
    Private Sub GenerateAgingList()
        Dim dao As New DAOReport.VW_RPT_AGING_UNPAID_PREMIUM_DAO
        Dim vo As New DAOReport.VW_RPT_AGING_UNPAID_PREMIUM_VO
        Dim strDate As String = ""

        With vo
            .NAME = "%" & txtClientName.Text & "%"
            .PRDT_DESC = "%" & txtProduct.Text & "%"
            .PROVIDER_DESC = "%" & txtProvider.Text & "%"
            If Trim(txtPolicyNo.Text) <> "" Then .POLICYNO = txtPolicyNo.Text
            .ADVISOR = "%" & txtAdvisor.Text & "%"
            If Not IsNothing(Session("advisor")) Then .ADVISOR = Session("UserName") & "%"
        End With

        If Trim(txtDate.Text) = "" Then
            strDate = CStr(Format(Now, "M/d/yyyy"))
        Else
            Dim cf As New CIMSfunc
            strDate = cf.ParseSetDate(txtDate.Text)
            cf = Nothing
        End If

        Dim dt As DataTable = dao.Search(vo, strDate, , True)
        DoRender(dt)

        dt = Nothing
        dao = Nothing
        vo = Nothing
    End Sub

#End Region

#Region "Birthday List"
    Private Sub GenerateBirthdayList()
        Dim dao As New DAOReport.VW_RPT_BIRTHDAY_LIST_DAO
        Dim vo As New DAOReport.VW_RPT_BIRTHDAY_LIST_VO
        Dim cf As New CIMSfunc

        'If Not IsNothing(Session("advisor")) Then vo.ADVISOR = Session("UserName") & "%"

        With vo
            Dim sDate As String = ""
            Dim edate As String = ""

            If Trim(Startdate_txt.Text) <> "" Then
                ' .BIRTHDAY = cf.ParseSetDate(Startdate_txt.Text)
                sDate = Left(Startdate_txt.Text, 6) & Str(Year(Now))
                .BIRTHDAY = cf.ParseSetDate(sDate)
            End If

            If Trim(Enddate_txt.Text) <> "" Then
                'edate = cf.ParseSetDate(Enddate_txt.Text)
                edate = Left(Enddate_txt.Text, 6) & Str(Year(Now))
                .BIRTHDAY_END = cf.ParseSetDate(edate)
            End If

            .CLIENTNAME = "%" & Client_txt.Text & "%"
            .POLICY_NO = "%" & Policynumber_txt.Text & "%"
            .PROVIDER_DESC = "%" & Provider_txt.Text & "%"
            .PRDT_DESC = "%" & Product_txt.Text & "%"
            If Trim(Status_cmb.SelectedItem.Text) <> "" Then .POLICY_ST = Status_cmb.SelectedItem.Text
            .ADVISOR = "%" & Advisor_txt.Text & "%"
            If Not IsNothing(Session("advisor")) Then .ADVISOR = Session("UserName") & "%"
        End With

        Dim dt As DataTable = dao.Search(vo, , True)
        'GridView1.DataSource = dt
        'GridView1.DataBind()
        DoRender(dt)

        dt = Nothing
        cf = Nothing
        dao = Nothing
        vo = Nothing
    End Sub
#End Region

#Region "Master List"

    Private Sub GenerateMasterList()
        Dim dao As New DAOReport.VW_RPT_MASTERLIST_DAO
        Dim vo As New DAOReport.VW_RPT_MASTERLIST_VO

        With vo
            .CLIENT_NAME = "%" & Client_txt.Text & "%"
            .PRDT_DESC = "%" & Product_txt.Text & "%"
            .PROVIDER_DESC = "%" & Provider_txt.Text & "%"
            If Trim(txtPolicyNo.Text) <> "" Then .POLICY_NO = Policynumber_txt.Text
            .ADVISOR = "%" & Advisor_txt.Text & "%"
            If Not IsNothing(Session("advisor")) Then .ADVISOR = Session("UserName") & "%"
            If Trim(Status_cmb.SelectedItem.Text) <> "" Then .POLICY_STATUS = Status_cmb.SelectedItem.Text
        End With

        Dim dt As DataTable = dao.Search(vo, , True)
        DoRender(dt)

        dt = Nothing
        dao = Nothing
        vo = Nothing
    End Sub

#End Region

#Region "Policy Issues"
    Private Sub GenerateIssues()
        Dim dao As New DAOMain.VW_POLICY_ISSUE_DTL_SEARCH_DAO
        Dim vo As New DAOMain.VW_POLICY_ISSUE_DTL_SEARCH_VO
        Dim cf As New CIMSfunc

        With vo
            If Trim(Startdate_txt.Text) <> "" Then .POLICY_ISSUE_DT = cf.ParseSetDate(Startdate_txt.Text)
            If Trim(Enddate_txt.Text) <> "" Then .POLICY_ISSUE_DT_END = cf.ParseSetDate(Enddate_txt.Text)
            .CLIENTNAME = "%" & Client_txt.Text & "%"
            .POLICY_NO = "%" & Policynumber_txt.Text & "%"
            .PROVIDER_DESC = "%" & Provider_txt.Text & "%"
            .PRDT_DESC = "%" & Product_txt.Text & "%"
            If Trim(Status_cmb.SelectedItem.Text) <> "" Then .STATUS = Status_cmb.SelectedItem.Text
            .ADVISOR = "%" & Advisor_txt.Text & "%"
            If Not IsNothing(Session("advisor")) Then .ADVISOR = Session("UserName") & "%"
        End With

        Dim sds As New DataSet
        Dim dt As DataTable = dao.Search(vo, , True)
        DoRender(dt)

        cf = Nothing
        dt = Nothing
        dao = Nothing
        vo = Nothing
    End Sub

    
#End Region

#Region "Commision Journal"
    Private Sub GenerateCommis()
        Dim dao As New DAOMain.VW_RPT_COMMISION_JOURNAL_DAO
        Dim vo As New DAOMain.VW_RPT_COMMISION_JOURNAL_VO
        Dim cf As New CIMSfunc

        With vo
            If Trim(Startdate_txt.Text) <> "" Then .COM_DT = cf.ParseSetDate(Startdate_txt.Text)
            If Trim(Enddate_txt.Text) <> "" Then .COM_DT_END = cf.ParseSetDate(Enddate_txt.Text)
            If Trim(Status_cmb.SelectedItem.Text) <> "" Then .STATUS = Me.Status_cmb.SelectedItem.Text
            .ADVISOR = "%" & Advisor_txt.Text & "%"
            If Not IsNothing(Session("advisor")) Then .ADVISOR = Session("UserName") & "%"
        End With

        Dim sds As New DataSet
        Dim dt As DataTable = dao.Search(vo, , True)

        DoRender(dt)

        cf = Nothing
        dt = Nothing
        dao = Nothing
        vo = Nothing
    End Sub
#End Region

#Region "New Policies on Client Servicing"
    Private Sub GeneratePolicySubmit()
        Dim vo As New DAOReport.VW_RPT_POLICY_SUBMIT_DTL_VO
        Dim dao As New DAOReport.VW_RPT_POLICY_SUBMIT_DTL_DAO
        Dim cf As New CIMSfunc

        With vo
            If Trim(Startdate_txt.Text) <> "" Then .POLICY_SUBMIT_DT = cf.ParseSetDate(Startdate_txt.Text)
            If Trim(Enddate_txt.Text) <> "" Then .END_DT = cf.ParseSetDate(Enddate_txt.Text)
            .NAME = "%" & Client_txt.Text & "%"
            .POLICY_NO = "%" & Policynumber_txt.Text & "%"
            .PROVIDER_DESC = "%" & Provider_txt.Text & "%"
            .PRDT_DESC = "%" & Product_txt.Text & "%"
            .ADVISOR = "%" & Advisor_txt.Text & "%"
            If Not IsNothing(Session("advisor")) Then .ADVISOR = Session("UserName") & "%"
            If Trim(Status_cmb.SelectedItem.Text) <> "" Then .STATUS = Status_cmb.SelectedItem.Text
        End With

        Dim dt As DataTable = dao.Search(vo, , True)

        DoRender(dt)

        cf = Nothing
        dt = Nothing
        dao = Nothing
        vo = Nothing
    End Sub
#End Region

#Region "vw_rpt_mailmerge"
    Private Sub GenerateMonthly()

        Dim vo As New DAOReport.VW_RPT_MAILMERGE_VO
        Dim dao As New DAOReport.VW_RPT_MAILMERGE_DAO
        Dim cf As New CIMSfunc

        With vo
            Select Case lblTitleDate.Text
                Case "Monthly Renewal List"
                    If Trim(Startdate_txt.Text) <> "" Then .COMPLETION_DT = cf.ParseSetDate(Startdate_txt.Text)
                    If Trim(Enddate_txt.Text) <> "" Then .COMPLETION_DT_END = cf.ParseSetDate(Enddate_txt.Text)
                    .POLICY_STATUS = "ACTIVE"
                Case Else
                    If Trim(Startdate_txt.Text) <> "" Then .BIRTHDAY = cf.ParseSetDate(Startdate_txt.Text)
                    If Trim(Enddate_txt.Text) <> "" Then .BIRTHDAY_END = cf.ParseSetDate(Enddate_txt.Text)
            End Select
            
            .PROVIDER_DESC = "%" & Provider_txt.Text & "%"
            .PRDT_DESC = "%" & Product_txt.Text & "%"
            .ADVISOR = "%" & Advisor_txt.Text & "%"
            If Not IsNothing(Session("advisor")) Then .ADVISOR = Session("UserName") & "%"

        End With

        Dim dt As DataTable = dao.Search(vo, , True)

        DoRender(dt)

        cf = Nothing
        dt = Nothing
        dao = Nothing
        vo = Nothing
    End Sub

#End Region

#Region "Monthly Renewal"
    Private Sub GenerateRenewal()
        Dim dao As New DAOReport.VW_RPT_MONTHLY_RENEWAL_DAO
        Dim vo As New DAOReport.VW_RPT_MONTHLY_RENEWAL_VO
        Dim cf As New CIMSfunc

        'Search Fields ***************************
        With vo
            If Trim(Startdate_txt.Text) <> "" Then .POLICY_DUE_DT = cf.ParseSetDate(Startdate_txt.Text)
            If Trim(Enddate_txt.Text) <> "" Then .POLICY_DUE_DT_END = cf.ParseSetDate(Enddate_txt.Text)

            .PROVIDER_DESC = "%" & Provider_txt.Text & "%"
            .PRDT_DESC = "%" & Product_txt.Text & "%"
            .ADVISOR_CD = "%" & Advisor_txt.Text & "%"
            If Not IsNothing(Session("advisor")) Then .ADVISOR_CD = Session("UserName") & "%"
            .POLICY_STATUS = "ACTIVE"
        End With
        '********************************************
        Dim dt As DataTable = dao.Search(vo, , True)

        DoRender(dt)

        dt = Nothing
        cf = Nothing
        dao = Nothing
        vo = Nothing
    End Sub
#End Region

End Class
